﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Clinica_Frba.CompradeBono
{
    public partial class Farmacia : Form
    {
        public Farmacia()
        {
            InitializeComponent();
        }
        public Farmacia(string idafil, string fecha,int nrocompra)
        {
            InitializeComponent();
            afiliado = idafil;
            fechac = fecha;
            compra = nrocompra;
        }
        string afiliado;
        string fechac;
        int compra;
        private void Farmacia_Load(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            string comando;
            comando = "SELECT id_med,descripcion FROM DUNDER_MIFFLIN.MEDICAMENTO";
            SqlCommand consulta = new SqlCommand(comando, myConnection);
            SqlDataReader da = consulta.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("id_med");
            dt.Columns.Add("descripcion");
            dt.Load(da);
            listBox1.DisplayMember = "descripcion";
            listBox1.ValueMember = "id_med";
            listBox1.DataSource = dt;
            listBox1.SelectedIndex = 0;
            myConnection.Close();
            consul = false;

        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (listBox1.Items.Count != 0)
            {
                bool ver = true;
                int i;
                if (listBox2.Items.Count > 5) { ver = false; }
                for (i = 0; i < listBox2.Items.Count; i++)
                {
                    if (listBox2.GetItemText(listBox2.Items[i]) == listBox1.GetItemText(listBox1.SelectedItem)) { ver = false; }
                }
                if (ver)
                {

                    listBox2.Items.Add(listBox1.GetItemText(listBox1.SelectedItem));

                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (listBox2.Items.Count != 0)
            {
                listBox2.Items.Remove(listBox2.SelectedItem);
                if (listBox2.Items.Count > 0) { listBox2.SelectedIndex = 0; }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string f = publico.fecha;
            if (consul) { } else { return; }
            if (listBox2.Items.Count < 1) { return; }
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            int ibono, iplan,i,idm;
            try
            {
                SqlCommand ini = new SqlCommand("BEGIN TRAN BF", myConnection);
                ini.ExecuteNonQuery();
                SqlCommand idb = new SqlCommand("SELECT ULTIMOID FROM DUNDER_MIFFLIN.PROVIDERID WHERE TABLA='BONO_FARMACIA'", myConnection);
                ibono = Convert.ToInt32(idb.ExecuteScalar());
                ibono++;
                SqlCommand idp = new SqlCommand("SELECT ID_PLAN FROM DUNDER_MIFFLIN.AFILIADO WHERE ID_AFIL=" + afiliado, myConnection);
                iplan = Convert.ToInt32(idp.ExecuteScalar());
                SqlCommand cslt = new SqlCommand("SELECT FECHA FROM DUNDER_MIFFLIN.ATENCION WHERE ID_ATENCION='" + textBox1.Text + "'", myConnection);
                string fecha = Convert.ToString(cslt.ExecuteScalar());
                SqlCommand com = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.BONO_FARMACIA(ID_BONO,GRUPO_FAMILIAR,ID_PLAN,USADO,NRO_CONSULTA,ID_CONSULTA,FECHA_PRESCRIPCION,FECHA_COMPRA,FECHA_IMPRESION,FECHA_VENCIMIENTO) VALUES(" + ibono + ",LEFT('" + afiliado + "',LEN('" + afiliado + "')-2)," + iplan + ",'N','" + textBox1.Text + "','" + textBox1.Text + "',CONVERT(datetime,'" + fecha + "',103),CONVERT(datetime,'" + fechac + "',103),CONVERT(datetime,'" + f + "',103),DATEADD(day,60,CONVERT(datetime,'" + fechac + "',103)))", myConnection);
                com.ExecuteNonQuery();
                SqlCommand updt = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + ibono + " WHERE TABLA='BONO_FARMACIA'", myConnection);
                updt.ExecuteNonQuery();
                SqlCommand bc = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.BONO_COMPRA(ID_BONO_F,ID_COMPRA)VALUES(" + ibono + "," + compra + ")", myConnection);
                bc.ExecuteNonQuery();
                for (i = 0; i < listBox2.Items.Count; i++)
                {
                    SqlCommand medicamento = new SqlCommand("SELECT ID_MED FROM DUNDER_MIFFLIN.MEDICAMENTO WHERE descripcion='" + listBox2.GetItemText(listBox2.Items[i]) + "'", myConnection);
                    idm = Convert.ToInt32(medicamento.ExecuteScalar());
                    SqlCommand insertmb = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.MEDICAMENTO_BONO(ID_MED,ID_BONOF) VALUES(" + idm + "," + ibono + ")", myConnection);
                    insertmb.ExecuteNonQuery();
                }
                SqlCommand plan = new SqlCommand("SELECT DESCRIPCION FROM DUNDER_MIFFLIN.PLAN_MEDICO WHERE ID_PLAN=" + iplan, myConnection);
                SqlCommand cmit = new SqlCommand("COMMIT TRAN BF", myConnection);
                cmit.ExecuteNonQuery();
                SqlCommand upd = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID="+ibono+" WHERE TABLA='BONO_FARMACIA'",myConnection);
                MessageBox.Show("Insercion de bono ok afiliado="+afiliado+" plan="+plan.ExecuteScalar()+" fecha de compra="+fechac, "Bono farmacia");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Bono farmacia");
                SqlCommand rb = new SqlCommand("ROLLBACK TRAN BF", myConnection);
                rb.ExecuteNonQuery();
            }
            this.Close();
        }

        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        bool consul;
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            consul = false;
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand buscar = new SqlCommand("SELECT ID_ATENCION FROM DUNDER_MIFFLIN.ATENCION WHERE ID_ATENCION='" + textBox1.Text + "'", myConnection);
            SqlDataReader dr = buscar.ExecuteReader();
            if (dr.HasRows) { consul = true; }
        }
    }
}
